import xlrd
import xlwt
from xlutils.copy import copy
import faker
class ExcelHandle:
    """
    一个将DataFrame转换成格式化excel的工具
    """
    def __init__(self, df_in, filename='a.xlsx'):
        """
        df_in : 从一个DataFrame对象获取表格内容
        filename : 文件名
        sheet_name : 表名
        """
        self.filename = filename  # 保存的xlsx文件的名字
        # self.user_def = []  # 储存由用户自定义的列的列名，这些列不再参与自动计算列宽
        # if path.exists(filename):
        #     # 如果文件存在，就直接打开，添加Sheet
        #     self.wb = load_workbook(filename)
        #     self.sheet = self.wb.create_sheet(sheet_name)
        # else:
        #     # 如果文件不存在，就创建表格
        #     self.wb = Workbook()
        #     self.sheet = self.wb.active
        #     self.sheet.title = sheet_name
        # # 将df的内容复制给sheet
        # self.df = df_in.copy()
        # self.sheet.append(list(self.df.columns))
        # for row in range(0, len(list(self.df.index))):
        #     for col in range(0, len(list(self.df.columns))):
        #         self.sheet.cell(row+2, col+1).value = self.df.iloc[row, col]  # 注意：sheet行列从1开始计数

    def add_sheet_data(wb, sheetname, head_data, dataList):
        # 表头内容填充(表格内容填充)
        sheet = wb.add_sheet(sheetname)
        for head in head_data:
            sheet.write(0, head_data.index(head), head)
        # 填充文档
        if (len(dataList) < 1):
            return wb;
        for i in range(1, len(dataList)):
            for j in range(0, len(dataList[i])):
                sheet.write(i, j, dataList[i][j])
        return wb

    def write_excel(filename, sheetList, head_data, dataList):
        # 表头内容填充(表格内容填充)
        wb = xlwt.Workbook()
        index = 0
        for sheetname in sheetList:
            sheet = wb.add_sheet(sheetname)
            for head in head_data:
                sheet.write(0, head_data.index(head), head)
            # 填充文档
            for i in range(1, len(dataList)):
                for j in range(0, len(dataList[i])):
                    sheet.write(i, j, dataList[i][j])
            index += 1
        wb.save(filename)

    def create_style(): # 定义字体格式，返回一个字体大小24，垂直居中 水平居中 宋体格式 的样式
        style = xlwt.XFStyle()
        fnt = xlwt.Font()                        # 创建一个文本格式，包括字体、字号和颜色样式特性
        fnt.name = u'宋体'
        fnt.height = 20*24
        alignment = xlwt.Alignment()
        alignment.horz = 0x02          # 0x01(左端对齐)、0x02(水平方向上居中对齐)、0x03(右端对齐)
        alignment.vert = 0x01          # 0x00(上端对齐)、 0x01(垂直方向上居中对齐)、0x02(底端对齐)
        style.font = fnt
        style.alignment = alignment
        return style

    def create_faker_excel_file(filename, department):
        wb = xlwt.Workbook(filename)
        sheet = wb.add_sheet('工资单')
        fake = faker.Faker("zh_CN")
        head_data = ['部门', '姓名', '工号', '薪资（元）', '迟到次数（次）', '奖金（元）', '实发工资']
        for head in head_data:
            sheet.write(0, head_data.index(head), head)
        for i in range(1, random.randint(5, 100)):
            sheet.write(i, 0, department)
            sheet.write(i, 1, fake.last_name() + fake.first_name())
            sheet.write(i, 2, "G{}".format(random.randint(1, 1000)))
            sheet.write(i, 3, random.randint(4000, 16000))
            sheet.write(i, 4, random.choice([0, 0, 0, 1, 2, 3, 4]))
            sheet.write(i, 5, random.choice([200, 300, 400, 500, 600, 700, 800, 900]))
        wb.save(filename)

    def build_faker_excel_file():
        department_name = ['技术部', '推广部', '客服部', '行政部', '财务部']
        for dep in department_name:
            xls_name = "{}-{}.xls".format(datetime.datetime.now().strftime("%Y-%m"), dep)
            create_faker_excel_file(xls_name, dep)
            print(xls_name, " 新建完成")